#include "adDao.h"
int addAd(Ad *ad){
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return -1;
    }
    char sql[256];
    sprintf(sql,"insert into t_ad(id,`index`,start_time,end_time) values(%d,%d,'%s','%s');",ad->id,ad->index,ad->start_time,ad->end_time);
    char *errMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errMsg);
    if (errMsg != NULL)
    {
        printf("%s\n",errMsg);
        return -1;
    }
    sqlite3_close(db);
    return 0;
}
int updateAd(Ad *ad){
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return -1;
    }
    char sql[256];
    sprintf(sql,"update t_ad set `index` = %d,start_time = '%s',end_time = '%s' where id = %d;",ad->index,ad->start_time,ad->end_time,ad->id);
    char *errMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errMsg);
    if (errMsg != NULL)
    {
        printf("%s\n",errMsg);
        return -1;
    }
    sqlite3_close(db);
    return 0;
}
Ad *getAd(int id){
    sqlite3 *db = NULL;
    //打开db
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return NULL;
    }
    char sql[256];
    sprintf(sql,"select * from t_ad where id = %d;",id);
    char *errMsg;
    int row;
    int column;
    //接收结果集
    char **result;
    res = sqlite3_get_table(db,sql,&result,&row,&column,&errMsg);
    if (res != SQLITE_OK)
    {
        printf("%s\n",errMsg);
    }
    int index = column;
    printf("查到%d条数据\n",row);
    for (int i = 0; i < row; i++)
    {
        Ad *ad = calloc(1,sizeof(Ad));
        for (int j = 0; j < column; j++)
        {
            char *colName = result[j];
            char *colVal = result[index];
            if (g_str_equal("id",colName))
            {
                ad->id = atoi(colVal);
            }
            if (g_str_equal("index",colName))
            {
                ad->index = atoi(colVal);
            }
            if (g_str_equal("start_time",colName))
            {
                ad->start_time = colVal;
            }
            if (g_str_equal("end_time",colName))
            {
                ad->end_time = colVal;
            }
            ++index;
        }
        return ad;
    }
    return NULL;
}
int addAdResource(AdResource *ar){
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return -1;
    }
    char sql[256];
    sprintf(sql,"insert into t_ad_resource(url,`index`,ad_id,sign,type_id) values('%s',%d,%d,'%s',%d);",ar->path,ar->index,ar->ad_id,ar->sign,ar->type_id);
    char *errMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errMsg);
    if (errMsg != NULL)
    {
        printf("%s\n",errMsg);
        return -1;
    }
    sqlite3_close(db);
    return 0;
}
int updateAdResource(AdResource *ar){
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return -1;
    }
    char sql[256];
    sprintf(sql,"update t_ad_resource set `index` = %d,url = '%s',sign = '%s',ad_id = %d,type_id = %d where id = %d;",ar->index,ar->path,ar->sign,ar->ad_id,ar->type_id,ar->id);
    char *errMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errMsg);
    if (errMsg != NULL)
    {
        printf("%s\n",errMsg);
        return -1;
    }
    sqlite3_close(db);
    return 0;
}
AdResource *getAdResource(int ad_id,int index){
    sqlite3 *db = NULL;
    //打开db
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return NULL;
    }
    char sql[256];
    sprintf(sql,"select * from t_ad_resource where ad_id = %d and `index`= %d;",ad_id,index);
    char *errMsg;
    int row;
    int column;
    //接收结果集
    char **result;
    res = sqlite3_get_table(db,sql,&result,&row,&column,&errMsg);
    if (res != SQLITE_OK)
    {
        printf("%s\n",errMsg);
    }
    int index1 = column;
    printf("查到%d条数据\n",row);
    for (int i = 0; i < row; i++)
    {
        AdResource *ar = calloc(1,sizeof(Ad));
        for (int j = 0; j < column; j++)
        {
            char *colName = result[j];
            char *colVal = result[index];
            if (g_str_equal("id",colName))
            {
                ar->id = atoi(colVal);
            }
            if (g_str_equal("index",colName))
            {
                ar->index = atoi(colVal);
            }
            if (g_str_equal("ad_id",colName))
            {
                ar->ad_id = atoi(colVal);
            }
            if (g_str_equal("type_id",colName))
            {
                ar->type_id = atoi(colVal);
            }
            if (g_str_equal("sign",colName))
            {
                ar->sign = colVal;
            }
            if (g_str_equal("url",colName))
            {
                ar->path = colVal;
            }
            ++index1;
        }
        return ar;
    }
    return NULL;
}
int delExpiredAds(){
    sqlite3 *db = NULL;
    int res = sqlite3_open(AD_DB_PATH,&db);
    if (res != 0)
    {
        perror("open db failed");
        return -1;
    }
    char sql[256];
    sprintf(sql,"delete from t_ad where end_time < datetime(CURRENT_TIMESTAMP,'localtime');");
    char *errMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errMsg);
    if (errMsg != NULL)
    {
        printf("%s\n",errMsg);
        return -1;
    }
    sprintf(sql,"delete from t_ad_resource where ad_id not in (select id from t_ad);");
    sqlite3_exec(db,sql,NULL,NULL,&errMsg);
    if (errMsg != NULL)
    {
        printf("%s\n",errMsg);
        return -1;
    }
    sqlite3_close(db);
}